#!/usr/bin/env python
# -*- coding: utf-8 -*-

from django.db import connection
from web.dao.base_dao import BaseDao
from web.models.commodity_future_hour_data import CommodityFutureHourData

"""
CommodityFutureHourData的dao类
"""


class CommodityFutureHourDataDao(BaseDao):
    model_class = CommodityFutureHourData

    def add_oracle_column_comment(self):
        """
        为表commodity_future_hour_data添加注释
        """

        with connection.cursor() as cursor:
            cursor.execute("comment on table commodity_future_hour_data is \'商品期货交易数据表（1小时级别）\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.id IS \'主键\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.code IS \'代码\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.begin_time IS \'开始时间\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.end_time IS \'结束时间\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.name IS \'名称\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.highest_price IS \'最高价\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.lowest_price IS \'最低价\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.open_price IS \'开盘价\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.close_price IS \'最新价/收盘价\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.last_close_price IS \'上个交易日收盘价\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.volume IS \'成交额\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.turnover IS \'成交量\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.open_interest IS \'持仓量\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.buying IS \'买盘（外盘）\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.selling IS \'卖盘（内盘）\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.rising_and_falling_amount IS \'涨跌额\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.price_change IS \'涨跌幅\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.ma5 IS \'5日均线\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.ma10 IS \'10日均线\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.ma20 IS \'20日均线\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.ma60 IS \'60日均线\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.ma120 IS \'120日均线\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.ma250 IS \'250日均线\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.ema12 IS \'MACD的ema12\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.ema26 IS \'MACD的ema26\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.dif IS \'MACD的dif\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.dea IS \'MACD的dea\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.rsv IS \'KD的rsv\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.k IS \'KD的k\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.d IS \'KD的d\'")
            cursor.execute("COMMENT ON COLUMN commodity_future_hour_data.create_time IS \'创建时间\'")

    ########################################### 全量计算数据 ########################################

    def write_hour_basic_data(self):
        """
        计算1小时级别全部last_close_price、rising_and_falling_amount和price_change字段
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_BASIC_DATA()")

    def write_hour_all_ma(self):
        """
        计算1小时级别全部MA数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.CALCULATE_FIVE()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.CALCULATE_TEN()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.CALCULATE_TWENTY()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.CALCULATE_SIXTY()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.CALCULATE_ONEHUNDREDTWENTY()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.CALCULATE_TWOHUNDREDFIFTY()")

    def write_hour_all_macd(self):
        """
        计算1小时级别全部MACD数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_MACD_INIT()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_MACD_EMA()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_MACD_DIF()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_MACD_DEA()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_MACD()")

    def write_hour_all_kd(self):
        """
        计算1小时级别全部KD数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_KD_INIT()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_KD_RSV()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_KD_K()")
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_KD_D()")

    def write_hour_all_boll(self):
        """
        计算1小时级别全部boll数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.CAL_BOLL()")

    def write_hour_all_hei_kin_ashi(self):
        """
        计算1小时级别全部hei_kin_ashi数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_HA()")

    def write_hour_all_bias(self):
        """
        计算1小时级别全部bias数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_BIAS()")

    def write_hour_all_variance(self):
        """
        计算1小时级别全部variance数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_VARIANCE()")

    def write_hour_unite_relative_price_index(self):
        """
        计算1小时级别全部unite_relative_price_index数据
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_COMMODITY_FUTURE_HOUR_DATA.CAL_UNITE_RELATIVE_PRICE_INDEX()")

    ########################################### 增量计算数据 ########################################
    def write_hour_basic_data_by_date(self, begin_time, end_time):
        """
        根据日期，计算1小时级别某一个交易日的last_close_price、rising_and_falling_amount和price_change字段
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_BASIC_HOUR_BY_DATE", (begin_time, end_time))

    def write_hour_unite_relative_price_index_by_date(self, begin_time, end_time):
        """
        根据日期，计算1小时级别某一个交易日的unite_relative_price_index数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_COMMODITY_FUTURE_HOUR_DATA.CAL_U_R_PRICE_INDEX_BY_DATE", (begin_time, end_time))

    def write_hour_ma_by_date(self, begin_time, end_time):
        """
        根据日期，计算1小时级别某一个交易日的MA数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_MOVING_AVERAGE_BY_DATE", (begin_time, end_time))

    def write_hour_macd_by_date(self, begin_time, end_time):
        """
        根据日期，计算1小时级别某一个交易日的MACD数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_MACD_EMA_BY_DATE", (begin_time, end_time))
            cursor.callproc("PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_MACD_DIF_BY_DATE", (begin_time, end_time))
            cursor.callproc("PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_MACD_DEA_BY_DATE", (begin_time, end_time))
            cursor.callproc("PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_MACD_BY_DATE", (begin_time, end_time))

    def write_hour_kd_by_date(self, begin_time, end_time):
        """
        根据日期，计算1小时级别某一个交易日的KD数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_KD_BY_DATE_RSV", (begin_time, end_time))
            cursor.callproc("PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_KD_BY_DATE_K", (begin_time, end_time))
            cursor.callproc("PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_KD_BY_DATE_D", (begin_time, end_time))

    def write_hour_hei_kin_ashi_by_date(self, begin_time, end_time):
        """
        根据日期，计算1小时级别某一个交易日的hei kin ashi数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_HA_BY_DATE", (begin_time, end_time))

    def write_hour_variance_by_date(self, begin_time, end_time):
        """
        根据日期，计算1小时级别某一个交易日的variance数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_VARIANCE_BY_DATE", (begin_time, end_time))

    def write_hour_boll_by_date(self, begin_time, end_time):
        """
        根据日期，计算1小时级别某一个交易日的BOLL数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_COMMODITY_FUTURE_HOUR_DATA.CAL_BOLL_BY_DATE", (begin_time, end_time))

    def write_hour_bias_by_date(self, begin_time, end_time):
        """
        根据日期，计算1小时级别某一个交易日的BIAS数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_COMMODITY_FUTURE_HOUR_DATA.WRITE_BIAS_BY_DATE", (begin_time, end_time))

    ########################################### 查询 ########################################
    def distinct_code(self):
        """
        返回code，去除重复的
        """

        with connection.cursor() as cursor:
            cursor.execute("select distinct t.code from commodity_future_hour_data t")
            _tuple = cursor.fetchall()
            code_list = list()
            for model in _tuple:
                code_list.append(model[0])
            return code_list
